package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import net.sf.json.JSONObject;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.TjrDao;

/**
 * 
 * @author zhai
 * @version 创建时间：2015年12月14日 上午9:34:42
 * @功能：抽奖结果页面
 */

/**
 * @author dell
 *
 */
@Repository
public class TjrDaoImpl extends P2PJdbcDao implements TjrDao {

	Logger logger = Logger.getLogger(TjrDaoImpl.class);

	/**
	 * @author zhai TjrDao 的实现类 获取推荐人的查询数据
	 */
	@Override
	public List querytjr(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String id = params.optString("id");
		String user_name = params.optString("user_name");
		String sjhm = params.optString("sjhm");
		String tjr = params.optString("tjr");
		String realName = params.optString("realName");
		String sfzNum = params.optString("sfzNum");
		String regStartDate = params.optString("regStartDate");
		String regEndDate = params.optString("regEndDate");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		/*sql.append(
				" SELECT t3.NEW_TJR,t1.ID, t1.USER_NAME, t1.SJHM, t1.TJR, t1.SFZ_NUM, t1.REAL_NAME,date_format(t1.CJRQ,'%Y-%m-%d %H:%i:%s') CJRQ, t1.CJ_TYPE ");
		sql.append(" ,t2.GYH OA_GYH,t2.DQ, t2.BM, t2.SS, t2.ZW, t2.ZJ, t2.XM OA_XM, t2.SJHM OA_SJHM ");
		sql.append(" FROM v_p2p_smrz t1 ");
		sql.append(" LEFT JOIN T_ZHFW_OA_USER t2 ON t1.TJR = t2.GYH ");
		sql.append(" LEFT JOIN t_zhfw_tjr_change_details t3 ON t1.USER_NAME=t3.USER_NAME ");*/
		sql .append("SELECT t3.NEW_TJR,t1.ID,  t1.SJHM,  t1.SFZ_NUM, "
				+"t1.REAL_NAME,date_format(t1.CJRQ,'%Y-%m-%d %H:%i:%s') CJRQ, t1.CJ_TYPE ,"
				+ "t2.GYH OA_GYH,t2.DQ, t2.BM, t2.SS, t2.ZW, t2.ZJ, t2.XM OA_XM, t2.SJHM OA_SJHM , "
				+" t4.TJJB ,t4.TJR,t4.USER_NAME ,t5.TGCODE,t5.TJR_TGCODE FROM v_p2p_smrz t1  LEFT JOIN T_ZHFW_OA_USER t2 ON t1.TJR = t2.GYH "
				+" LEFT JOIN t_zhfw_tjr_change_details t3 ON t1.USER_NAME=t3.USER_NAME "
				+" left join t_zhfw_tjr_tjjb t4 on t1.USER_NAME=t4.USER_NAME left join t_zhfw_user_tgcode t5 on t5.USER_NAME=t1.USER_NAME");
		sql.append("   where 1=1 ");
		if (StringUtils.isNotBlank(regStartDate)) {
			sql.append(" AND date_format(t1.CJRQ,'%Y-%m-%d') >= :regStartDate ");
			paramMap.put("regStartDate", regStartDate);
		}
		if (StringUtils.isNotBlank(regEndDate)) {
			sql.append(" AND date_format(t1.CJRQ,'%Y-%m-%d') <= :regEndDate ");
			paramMap.put("regEndDate", regEndDate);
		}
		if (StringUtils.isNotEmpty(id)) {
			sql.append(" AND t1.USER_NAME like :id");
			paramMap.put("id", "%" + id + "%");
		}
		if (StringUtils.isNotEmpty(user_name)) {
			sql.append(" AND t1.USER_NAME like :username");
			paramMap.put("username", "%" + user_name + "%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" AND t1.SJHM like :sjhm");
			paramMap.put("sjhm", "%" + sjhm + "%");
		}
		if (StringUtils.isNotEmpty(tjr)) {
			String[] tjrs = StringUtils.split(tjr, ",");
			String[] sqls = new String[tjrs.length];
			for (int i = 0; i < tjrs.length; i++) {
				sqls[i] = "t1.TJR like :tjr" + i;
				paramMap.put("tjr" + i, tjrs[i] + "%");
			}
			sql.append(" AND (" + StringUtils.join(sqls, " OR ") + ")");
		}
		if (StringUtils.isNotEmpty(realName)) {
			sql.append(" AND t1.REAL_NAME like :realName");
			paramMap.put("realName", "%" + realName + "%");
		}
		if (StringUtils.isNotEmpty(sfzNum)) {
			sql.append(" AND t1.SFZ_NUM like :sfzNum");
			paramMap.put("sfzNum", "%" + sfzNum + "%");
		}
		sql.append(" order by CJRQ desc ");

		if (0 != limit) {
			sql.append(" LIMIT :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("querytjr sql is " + sql.toString());
		logger.info("querytjr paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);

	}

	/**
	 * 、
	 * 
	 * @author zhai 获取查询的数据个数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int gettjrCount(JSONObject params) {
		String id = params.optString("id");
		String user_name = params.optString("user_name");
		String sjhm = params.optString("sjhm");
		String tjr = params.optString("tjr");
		String realName = params.optString("realName");
		String sfzNum = params.optString("sfzNum");
		String regStartDate = params.optString("regStartDate");
		String regEndDate = params.optString("regEndDate");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT count(*) FROM v_p2p_smrz t1 ");
		sql.append(" LEFT JOIN T_ZHFW_OA_USER t2 ON t1.TJR = t2.GYH ");
		sql.append(" LEFT JOIN t_zhfw_tjr_change_details t3 ON t1.USER_NAME=t3.USER_NAME left join t_zhfw_tjr_tjjb t4 on t1.USER_NAME=t4.USER_NAME");
		sql.append("   where 1=1 ");
		if (StringUtils.isNotBlank(regStartDate)) {
			sql.append(" AND date_format(t1.CJRQ,'%Y-%m-%d') >= :regStartDate ");
			paramMap.put("regStartDate", regStartDate);
		}
		if (StringUtils.isNotBlank(regEndDate)) {
			sql.append(" AND date_format(t1.CJRQ,'%Y-%m-%d') <= :regEndDate ");
			paramMap.put("regEndDate", regEndDate);
		}
		if (StringUtils.isNotEmpty(id)) {
			sql.append(" AND t1.USER_NAME like :id");
			paramMap.put("id", "%" + id + "%");
		}
		if (StringUtils.isNotEmpty(user_name)) {
			sql.append(" AND t1.USER_NAME like :username");
			paramMap.put("username", "%" + user_name + "%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" AND t1.SJHM like :sjhm");
			paramMap.put("sjhm", "%" + sjhm + "%");
		}
		if (StringUtils.isNotEmpty(tjr)) {
			String[] tjrs = StringUtils.split(tjr, ",");
			String[] sqls = new String[tjrs.length];
			for (int i = 0; i < tjrs.length; i++) {
				sqls[i] = "t1.TJR like :tjr" + i;
				paramMap.put("tjr" + i, tjrs[i] + "%");
			}
			sql.append(" AND (" + StringUtils.join(sqls, " OR ") + ")");
		}
		if (StringUtils.isNotEmpty(realName)) {
			sql.append(" AND t1.REAL_NAME like :realName");
			paramMap.put("realName", "%" + realName + "%");
		}
		if (StringUtils.isNotEmpty(sfzNum)) {
			sql.append(" AND t1.SFZ_NUM like :sfzNum");
			paramMap.put("sfzNum", "%" + sfzNum + "%");
		}
		logger.info("gettjrCount sql is " + sql.toString());
		logger.info("gettjrCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);

	}

	@Override
	public int updateTjr(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		String id = parms.optString("id");
		String xtjr = parms.optString("xtjr");
		sql.append(" UPDATE t_zhfw_user SET TJR=:TJR WHERE ID=:ID ");
		Map<String, String> paramsMap = new HashMap<String, String>();
		paramsMap.put("ID", id);
		paramsMap.put("TJR", xtjr);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramsMap);
	}

	@Override
	public int addTjr(JSONObject parms) {

		StringBuffer sql = new StringBuffer();
		// String id = parms.optString("id");
		String name = parms.optString("name");
		String sjhm = parms.optString("sjhm");
		String ytjr = parms.optString("ytjr");
		String xtjr = parms.optString("xtjr");
		String gyh = parms.optString("gyh");
		System.out.println(gyh);

		sql.append(
				" INSERT INTO t_zhfw_tjr_change_details (ID, USER_NAME, SJHM, OLD_TJR,NEW_TJR,OPER_USER_NAME,BG_DATE ) VALUES (uuid(),:name,:sjhm,:ytjr,:xtjr,:gyh, now() )");
		Map<String, String> paramsMap = new HashMap<String, String>();
		// paramsMap.put("id", id);
		paramsMap.put("name", name);
		paramsMap.put("sjhm", sjhm);
		paramsMap.put("ytjr", ytjr);
		paramsMap.put("xtjr", xtjr);
		paramsMap.put("gyh", gyh);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramsMap);
	}

	@Override
	public List queryxTjr(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");

		String name = params.optString("name");
		String sjhm = params.optString("sjhm");
		String ytjr = params.optString("ytjr");
		String xtjr = params.optString("xtjr");
		String xgr = params.optString("xgr");
		String regStartDate = params.optString("regStartDate");
		String regEndDate = params.optString("regEndDate");

		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("USER_NAME", name);
		paramMap.put("SJHM", sjhm);
		paramMap.put("YTJR", ytjr);
		paramMap.put("NEW_TJR", xtjr);
		paramMap.put("OPER_USER_NAME", xgr);
		/*sql.append(
				" SELECT USER_NAME,SJHM,OLD_TJR,NEW_TJR,OPER_USER_NAME,date_format(BG_DATE,'%Y-%m-%d %H:%i:%s') BG_DATE");
		sql.append(" FROM t_zhfw_tjr_change_details ");*/
		sql.append("SELECT t.USER_NAME,t.SJHM,t.OLD_TJR,t.NEW_TJR,t.OPER_USER_NAME,"
				+ " date_format(t.BG_DATE,'%Y-%m-%d %H:%i:%s') BG_DATE,t1.TJJB ,t1.TJR,t2.TGCODE,t2.TJR_TGCODE"
				+ " FROM t_zhfw_tjr_change_details t left join t_zhfw_tjr_tjjb t1 "
				+ " on t.USER_NAME=t1.USER_NAME left join t_zhfw_user_tgcode t2 on t2.USER_NAME=t.USER_NAME ");
		sql.append("   where 1=1 ");
		if (StringUtils.isNotBlank(regStartDate)) {
			sql.append(" AND date_format(BG_DATE,'%Y-%m-%d %H:%i:%s') >= :regStartDate ");
			paramMap.put("regStartDate", regStartDate + " 00:00:00");
		}
		if (StringUtils.isNotBlank(regEndDate)) {
			sql.append(" AND date_format(BG_DATE,'%Y-%m-%d %H:%i:%s') <= :regEndDate ");
			paramMap.put("regEndDate", regEndDate + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(name)) {
			sql.append(" AND USER_NAME like " + "'%" + name + "%'");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" AND SJHM like " + "'%" + sjhm + "%'");
		}
		if (StringUtils.isNotEmpty(ytjr)) {
			sql.append(" AND OLD_TJR like " + "'%" + ytjr + "%'");
		}
		if (StringUtils.isNotEmpty(xtjr)) {
			sql.append(" AND NEW_TJR like " + "'%" + xtjr + "%'");
		}
		if (StringUtils.isNotEmpty(xgr)) {
			sql.append(" AND OPER_USER_NAME like " + "'%" + xgr + "%'");
		}
		if (0 != limit) {
			sql.append("order by BG_DATE desc LIMIT :START,:LIMIT");
		}
		logger.info("queryxTjr sql is " + sql.toString());
		logger.info("queryxTjr paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/**
	 * 查询最新推荐人 个数 qjy
	 * 
	 */
	@Override
	public int tjrxcount(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");

		String name = params.optString("name");
		String sjhm = params.optString("sjhm");
		String ytjr = params.optString("ytjr");
		String xtjr = params.optString("xtjr");

		String regStartDate = params.optString("regStartDate");
		String regEndDate = params.optString("regEndDate");

		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("name", name);
		paramMap.put("SJHM", sjhm);
		paramMap.put("OLD_TJR", ytjr);
		paramMap.put("NEW_TJR", xtjr);
		sql.append(" SELECT count(*)");
		sql.append(" FROM t_zhfw_tjr_change_details t left join t_zhfw_tjr_tjjb t1 on t.USER_NAME=t1.USER_NAME ");
		sql.append("   where 1=1 ");
		if (StringUtils.isNotBlank(regStartDate)) {
			sql.append(" AND date_format(BG_DATE,'%Y-%m-%d %H:%i:%s') >= :regStartDate ");
			paramMap.put("regStartDate", regStartDate + " 00:00:00");
		}
		if (StringUtils.isNotBlank(regEndDate)) {
			sql.append(" AND date_format(BG_DATE,'%Y-%m-%d %H:%i:%s') <= :regEndDate ");
			paramMap.put("regEndDate", regEndDate + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(name)) {
			sql.append(" AND USER_NAME like " + "'%" + name + "%'");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" AND SJHM like " + "'%" + sjhm + "%'");
		}
		if (StringUtils.isNotEmpty(ytjr)) {
			sql.append(" AND  OLD_TJR like " + "'%" + ytjr + "%'");
		}
		if (StringUtils.isNotEmpty(xtjr)) {
			sql.append(" AND NEW_TJR like " + "'%" + xtjr + "%'");
		}

		logger.info("gettjrCount sql is " + sql.toString());
		logger.info("gettjrCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public Map<String, String> getTjrInfo(String username) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t3.TJR,t3.USER_NAME FROM t_zhfw_user_tgcode t1");
		sql.append(" left join t_zhfw_user_tgcode t2 on t1.TJR_TGCODE=t2.TGCODE");
		sql.append(" left join t_zhfw_user t3 on t2.USER_NAME=t3.USER_NAME");
		sql.append(" where t1.USER_NAME='" + username + "'");
		return this.getNamedParameterJdbcTemplate().query(sql.toString(),
				new ResultSetExtractor<Map<String, String>>() {

					@Override
					public Map<String, String> extractData(ResultSet resultSet)
							throws SQLException, DataAccessException {
						Map<String, String> result = new HashMap<>();
						while (resultSet.next()) {
							result.put("TJR", resultSet.getString("TJR"));
							result.put("USER_NAME", resultSet.getString("USER_NAME"));
						}
						return result;
					}
				});
	}

	@Override
	public boolean isFirstTjr(String username) {
		String sql = "select TJR from t_zhfw_user where USER_NAME='" + username + "'";
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<Boolean>() {

			@Override
			public Boolean extractData(ResultSet resultSet) throws SQLException, DataAccessException {
				String tjr = null;
				while (resultSet.next()) {
					tjr = resultSet.getString("TJR");
				}
				if (StringUtils.isNotBlank(tjr)) {
					return true;
				}
				return false;
			}
		});
	}

	@Override
	public List<Map<String, Object>> listUserTgInfo(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String tjr = params.optString("tjr");
		String tgCode = params.optString("tgCode");
		String tjjb = params.optString("tjjb");
		StringBuffer sql = new StringBuffer();
		/*sql.append(
				"select t1.ID,t1.USER_NAME,t2.SJHM,date_format(t1.UPDARE_DATE,'%Y-%m-%d') CJRQ,t2.TGCODE,t2.TJR_TGCODE,t1.TJR");
		sql.append(",t1.TJJB from t_zhfw_tjr_info t1 left join t_zhfw_user_tgcode t2 on t1.USER_NAME=t2.USER_NAME");
		*/
		sql.append("select t1.ID,t1.USER_NAME,t2.SJHM,date_format(t1.UPDARE_DATE,'%Y-%m-%d') CJRQ,"
				+ " t2.TGCODE,t2.TJR_TGCODE,t1.TJR,t1.TJJB,t3.TJJB from t_zhfw_tjr_info t1 "
				+ " left join t_zhfw_user_tgcode t2 on t1.USER_NAME=t2.USER_NAME "
				+ " left join t_zhfw_tjr_tjjb t3 on t1.USER_NAME=t3.USER_NAME");
		sql.append(" where 1=1");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t1.UPDARE_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t1.UPDARE_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and t1.USER_NAME like :username");
			paramMap.put("username", "%" + username + "%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and t2.SJHM like :sjhm");
			paramMap.put("sjhm", "%" + sjhm + "%");
		}
		if (StringUtils.isNotEmpty(tjr)) {
			sql.append(" and t2.OA_GYH like :tjr");
			paramMap.put("tjr", "%" + tjr + "%");
		}
		if (StringUtils.isNotEmpty(tgCode)) {
			sql.append(" and t2.TGCODE like :tgCode");
			paramMap.put("tgCode", "%" + tgCode + "%");
		}
		if (StringUtils.isNotEmpty(tjjb)) {
			sql.append(" and t1.TJJB = :tjjb");
			paramMap.put("tjjb", tjjb);
		}
		sql.append(" order by t1.UPDARE_DATE desc");
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listUserTgInfo sql is " + sql.toString());
		logger.info("listUserTgInfo paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@SuppressWarnings("deprecation")
	@Override
	public int getUserTgCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String tjr = params.optString("tjr");
		String tgCode = params.optString("tgCode");
		String tjjb = params.optString("tjjb");
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from t_zhfw_tjr_info t1 left join t_zhfw_user_tgcode t2 on t1.USER_NAME=t2.USER_NAME left join t_zhfw_tjr_tjjb t3 on t1.USER_NAME=t3.USER_NAME");
		sql.append(" where 1=1 ");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t1.UPDARE_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate + " 00:00:00");
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t1.UPDARE_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate + " 23:59:59");
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and t1.USER_NAME like :username");
			paramMap.put("username", "%" + username + "%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and t2.SJHM like :sjhm");
			paramMap.put("sjhm", "%" + sjhm + "%");
		}
		if (StringUtils.isNotEmpty(tjr)) {
			sql.append(" and t2.OA_GYH like :tjr");
			paramMap.put("tjr", "%" + tjr + "%");
		}
		if (StringUtils.isNotEmpty(tgCode)) {
			sql.append(" and t2.TGCODE like :tgCode");
			paramMap.put("tgCode", "%" + tgCode + "%");
		}
		if (StringUtils.isNotEmpty(tjjb)) {
			sql.append(" and t1.TJJB = :tjjb");
			paramMap.put("tjjb", tjjb);
		}
		logger.info("getUserTgCount sql is " + sql.toString());
		logger.info("getUserTgCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	/**
	 * @作者 js  
	 * @方法名 getOaInfoByGyh  
	 * @功能 TODO 获取客户推荐人和人推荐级别
	 * @param username
	 * @param tjjb
	 * @return
	 */
	private Map<String, Object> getTjjbByGyh(String username, int tjjb) {
		//logger.info("getTjjbByGyh 获取用户推荐人,推荐级别信息..." + username);
		Map<String, Object> paramMap = new HashMap<>();
		Map<String, Object> result = new HashMap<>();
		result.put("USER_NAME", username);
		String sql = "select TJR from t_zhfw_user where DISABLED='1' and USER_NAME=:username";
		for (int i = 0; i < tjjb; i++) {
			paramMap.put("username", username);
			/*try {
				result = this.getNamedParameterJdbcTemplate().queryForMap(sql, paramMap);
			} catch (EmptyResultDataAccessException e) {
				result.put("TJR", null);
			}*/
			result = this.getNamedParameterJdbcTemplate().query(sql, paramMap, new ResultSetExtractor<Map<String, Object>>() {

				@Override
				public Map<String, Object> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
					Map<String, Object> result = new HashMap<>();
					while (resultSet.next()) {
						String tjr=resultSet.getString("TJR");
						if (StringUtils.isEmpty(tjr)||"null".equals(tjr)) {
							result.put("TJR", null);
						} else {
							result.put("TJR", tjr);
						}
					}
					return result;
				}
			});
			result.put("TJJB", null);
			if (MapUtils.isEmpty(result) || StringUtils.isBlank(MapUtils.getString(result, "TJR"))) {
				String sql_tg = "select t3.USER_NAME from t_zhfw_user_tgcode t1 left join t_zhfw_user_tgcode t2"
						+ " on t1.TJR_TGCODE=t2.TGCODE left join t_zhfw_user t3 on t2.USER_NAME=t3.USER_NAME"
						+ " where t1.USER_NAME='"+ username + "'";
				String user_name = this.getSingleValue(sql_tg);
				if (StringUtils.isNotBlank(user_name)) {
					username = user_name;
				} else {
					break;
				}
			} else {
				result.put("TJJB", i + 1);
				break;
			}
		}
		return result;
	}
	/**
	 * @作者 js
	 * @方法名 getSingleValue
	 * @功能 TODO 根据sql获取单个字段值
	 * @param sSQL
	 * @return
	 */
	private String getSingleValue(String sSQL) {
		return this.getNamedParameterJdbcTemplate().query(sSQL, new ResultSetExtractor<String>() {
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String dx = "";
				// 获取字段数
				while (rs.next()) {
					dx = rs.getString(1);
				}
				return dx;
			}
		});
	}

	@Override
	public List<String> listUserInfo() {
		String sql = "select USER_NAME from t_zhfw_user where DISABLED='1'";
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<List<String>>() {

			@Override
			public List<String> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
				List<String> result=new ArrayList<>();
				while (resultSet.next()) {
					result.add(resultSet.getString("USER_NAME"));
				}
				return result;
			}
		});
	}

	@Override
	public boolean isNeedUpdate(String username) {
		Map<String, Object> tjrInfo=getTjjbByGyh(username, 3);
		String sql="select TJR,TJJB from t_zhfw_tjr_info where USER_NAME=:username";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("username", username);
		Map<String, Object>	queryInfo=new HashMap<>();
		try {
			queryInfo =this.getNamedParameterJdbcTemplate().queryForMap(sql, paramMap);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return true;
		}
		if (StringUtils.equals(MapUtils.getString(tjrInfo, "TJR"), MapUtils.getString(queryInfo, "TJR"))) {
			if (StringUtils.equals(MapUtils.getString(tjrInfo, "TJJB"), MapUtils.getString(queryInfo, "TJJB"))) {
				return false;
			} else {
				return true;
			}
		}else {
			return true;
		}
	}

	@SuppressWarnings("deprecation")
	@Override
	public boolean updateTjrInfo(String username) {
		Map<String, Object> tjrInfo=getTjjbByGyh(username, 3);
		tjrInfo.put("USER_NAME", username);
		String sql0="select count(*) from t_zhfw_tjr_info where USER_NAME=:USER_NAME";
		String sql1="insert into t_zhfw_tjr_info(ID,USER_NAME,TJR,TJJB,UPDARE_DATE) values(uuid(),:USER_NAME,:TJR,:TJJB,NOW())";
		String sql2="update t_zhfw_tjr_info set TJR=:TJR,TJJB=:TJJB,UPDARE_DATE=now() where USER_NAME=:USER_NAME";
		int count=0;
		if (this.getNamedParameterJdbcTemplate().queryForInt(sql0, tjrInfo)>0) {
			count=this.getNamedParameterJdbcTemplate().update(sql2, tjrInfo);
		}else {
			count=this.getNamedParameterJdbcTemplate().update(sql1, tjrInfo);
		}
		if (count>0) {
			return true;
		}
		return false;
	}

	@Override
	public List<Map<String, String>> listUsernameAndTjr(String username) {
		String sql="select t1.USER_NAME,t1.TJR from t_zhfw_user t1 left join t_zhfw_tjr_tjjb t2 on t1.USER_NAME=t2.USER_NAME"
				+ " where t1.DISABLED='1' and t2.USER_NAME ='"+username+"' or t2.TJR_USERNAME='"+username+"' or"
						+ " t2.TJR_USERNAME_2='"+username+"'";
		logger.info("listUsernameAndTjr sql is "+sql);
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<List<Map<String,String>>>() {

			@Override
			public List<Map<String, String>> extractData(ResultSet rs) throws SQLException, DataAccessException {
				List<Map<String, String>> list=new ArrayList<>();
				Map<String,String> map;
				while (rs.next()) {
					map =new HashMap<>();
					String username=rs.getString("USER_NAME");
					if (StringUtils.isEmpty(username)) {
						username="";
					}
					map.put("USER_NAME", username);
					String tjr=rs.getString("TJR");
					if (StringUtils.isEmpty(tjr)) {
						tjr="";
					}
					map.put("TJR", tjr);
					list.add(map);
				}
				return list;
			}
		});
	}

	@Override
	public Map<String, String> getTjjbMap(String username) {
		String sql="select t1.TGCODE,t1.TJR_TGCODE,t2.USER_NAME,t3.TJR from t_zhfw_user_tgcode t1 "
				+ "left join t_zhfw_user_tgcode t2 on t1.TJR_TGCODE=t2.TGCODE left join t_zhfw_user t3 "
				+ "on t2.USER_NAME=t3.USER_NAME where t1.USER_NAME='"+username+"'";
		logger.info("getTjjbMap sql is "+sql);
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<Map<String, String>>() {

			@Override
			public Map<String, String> extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map<String, String> result=new HashMap<>();
				while (rs.next()) {
					result.put("TGCODE", rs.getString("TGCODE"));
					result.put("TJR_TGCODE", rs.getString("TJR_TGCODE"));
					result.put("USER_NAME", rs.getString("USER_NAME"));
					result.put("TJR", rs.getString("TJR"));
				}
				return result;
			}
		});
	}

	@Override
	public int deleteTjjb(String username) {
		String sql="delete from t_zhfw_tjr_tjjb where USER_NAME ='"+username+"'";
		Map<String, Object> paramMap=new HashMap<>();
		logger.info("deleteTjjb sql is "+sql);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int saveTjjb(Map<String, String> paramMap) {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into t_zhfw_tjr_tjjb(USER_NAME,TJR_1,TGCODE,TJR_TGCODE,TJR_USERNAME,TJR_2");
		sql.append(",TJR_TGCODE_2,TJR_USERNAME_2,TJR_3,TJR,TJJB,UPDATE_DATE) values(:USER_NAME,:TJR_1,:TGCODE");
		sql.append(",:TJR_TGCODE,:TJR_USERNAME,:TJR_2,:TJR_TGCODE_2,:TJR_USERNAME_2,:TJR_3,:TJR,:TJJB,now())");
		logger.info("saveTjjb sql is "+sql.toString());
		logger.info("saveTjjb paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}
}
